package czy.demo.service;


import czy.demo.pojo.Person;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;

import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Date;
import java.util.List;

@Service
public class JdbcTemplateService{

    private static Logger logger = LoggerFactory.getLogger(JdbcTemplateService.class);

    @Autowired
    private JdbcTemplate jdbcTemplate;

    /* 从结果集中获取Person对象 */
    public Person getOneFromResultSet(ResultSet rs)throws SQLException, DataAccessException{
        Person person = new Person();
        person.setId(rs.getInt("id"));
        person.setName(rs.getString("name"));
        person.setAge(rs.getInt("age"));
        /* Timestamp为Date的子类 */
        person.setCreateTime(rs.getTimestamp("create_time"));
        return person;
    }

    /* ResultSetExtractor映射结果集 */
    public Person getOneByIdWithResultSetExtractor(Integer id){
        /* 可以使用预编译sql语句，即在sql中使用？代替参数，将参数以数组形式传递 */
        return this.jdbcTemplate.query("select * from person where id=?",new Object[]{id}, new ResultSetExtractor<Person>() {
            @Override
            public Person extractData(ResultSet rs) throws SQLException, DataAccessException {
                rs.next();
                return getOneFromResultSet(rs);
            }
        });
    }

    /**
     *  RowMapper映射结果集，它多出一个参数rowNum
     *  按照字面意思rowNum代表行号，其实它一直都是0
     *  如果查询结果返回多条数据，会抛出异常，结果集大小错误
     */
    public Person getOneByIdWithRowMapper(Integer id){
        return this.jdbcTemplate.queryForObject("select * from person where id="+id, new RowMapper<Person>() {
            @Override
            public Person mapRow(ResultSet rs, int rowNum) throws SQLException {
                logger.info("当前数据行号为："+rowNum);
                /* 这个方法中ResultSet的下标已经到位，不可以调用next */
                return getOneFromResultSet(rs);
            }
        });

    }

    /* 使用类的方式获取数据 */
    public Person getOneByIdWithClass(Integer id){
        /**
         * queryForObject方法一次只能获取一列的数据，这里的Class必须是基本数据类型
         * 如Integer、String、Timestamp等
         * queryForList方法貌似也一样，也就是说JdbcTemplate不能直接将一行数据转换为pojo对象
         * 要么自定义转换规则，要么只能获取简单类型数据
         */
        String name = this.jdbcTemplate.queryForObject("select name from person where id=?",new Object[]{id},String.class);
        Integer age = this.jdbcTemplate.queryForObject("select age from person where id=?",new Object[]{id},Integer.class);
        Date createTime = this.jdbcTemplate.queryForObject("select create_time from person where id=?",new Object[]{id},Timestamp.class);
        Person person = new Person();
        person.setId(id);
        person.setName(name);
        person.setAge(age);
        person.setCreateTime(createTime);
        return person;
    }

    /* 获取Person列表方法，这里使用RowMapper */
    public List<Person> getAllPerson(){
        /* 不需要调用rs.next()，结果集下标自动调整 */
        return this.jdbcTemplate.query("select * from person ", new RowMapper<Person>() {
            @Override
            public Person mapRow(ResultSet rs, int rowNum) throws SQLException {
                /* 结果集有多行时行号从0开始 */
                logger.info("当前数据行号为："+rowNum);
                return getOneFromResultSet(rs);
            }
        });
    }

    /* 更新person，返回更新条目数量 */
    public int updateOne(Person person){
        return this.jdbcTemplate.update("update person set name = ? , age = ? , create_time = ? where id = ?",new Object[]{person.getName(),person.getAge(),person.getCreateTime(),person.getId()});
    }

    public void deleteOne(Integer id){
        /* JdbcTemplate貌似没有删除方法，需要构造sql执行 */
        this.jdbcTemplate.execute("delete from person where id="+id);
    }

    /* 调用存储过程方法 */
    public void call(){
        this.jdbcTemplate.execute("get", new CallableStatementCallback<Person>() {
            @Override
            public Person doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
                return null;
            }
        });
    }

}
